package com.coderman.generateSqlBatchTools.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.text.NumberFormat;
import java.util.*;

public class PoiExcelUtils {

	private static NumberFormat format = NumberFormat.getInstance();

	/** 日志 */
	private static final Logger LOGGER = Logger.getLogger(PoiExcelUtils.class);
	/** 列默认宽度 */
	private static final int DEFAUL_COLUMN_WIDTH = 4000;

	private static FileInputStream is = null;
	private static XSSFWorkbook wb = null;

	/**
	 * 写入表头信息
	 * 
	 * @param XSSFWorkbook
	 * @param XSSFSheet
	 * @param headers
	 *            列标题，数组形式，
	 *            如{"列标题1@beanFieldName1@columnWidth","列标题2@beanFieldName2@columnWidth","列标题3@beanFieldName3@columnWidth"
	 *            } 其中参数@columnWidth可选，columnWidth为整型数值
	 * @param title
	 *            标题
	 */
	public static void writeHeader(XSSFWorkbook XSSFWorkbook, XSSFSheet XSSFSheet, String[] headers, String title) {
		LOGGER.info("【写入表头信息】");

		// 头信息处理
		String[] newHeaders = headersHandler(headers);

		// 初始化标题和表头单元格样式
		XSSFCellStyle titleCellStyle = createTitleCellStyle(XSSFWorkbook);
		// 标题栏
		XSSFRow titleRow = XSSFSheet.createRow(0);
		titleRow.setHeight((short) 500);
		XSSFCell titleCell = titleRow.createCell(0);
		// 设置标题文本
		titleCell.setCellValue(new XSSFRichTextString(title));
		// 设置单元格样式
		titleCell.setCellStyle(titleCellStyle);

		// 处理单元格合并，四个参数分别是：起始行，终止行，起始行，终止列
		XSSFSheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) (newHeaders.length - 1)));

		// 设置合并后的单元格的样式
		titleRow.createCell(newHeaders.length - 1).setCellStyle(titleCellStyle);

		// 表头
		XSSFRow headRow = XSSFSheet.createRow(1);
		headRow.setHeight((short) 500);
		XSSFCell headCell = null;
		String[] headInfo = null;
		// 处理excel表头
		for (int i = 0, len = newHeaders.length; i < len; i++) {
			headInfo = newHeaders[i].split("@");
			headCell = headRow.createCell(i);
			headCell.setCellValue(headInfo[0]);
			headCell.setCellStyle(titleCellStyle);
			// 设置列宽度
			setColumnWidth(i, headInfo, XSSFSheet);
		}
	}

	/**
	 * 头信息校验和处理
	 * 
	 * @param headers
	 */
	private static String[]  headersHandler(String[] headers) {
		List<String> newHeaders = new ArrayList<String>();
		for (String string : headers) {
			if (StringUtils.isNotBlank(string)) {
				newHeaders.add(string);
			}
		}
		int size = newHeaders.size();

		return newHeaders.toArray(new String[size]);
	}


	/**
	 * 设置列宽度
	 *
	 * @param i
	 *            列的索引号
	 * @param headInfo
	 *            表头信息，其中包含了用户需要设置的列宽
	 */
	private static void setColumnWidth(int i, String[] headInfo, XSSFSheet XSSFSheet) {

		if (headInfo.length < 2) {
			// 用户没有设置列宽，使用默认宽度
			XSSFSheet.setColumnWidth(i, DEFAUL_COLUMN_WIDTH);
			return;
		}
		if (StringUtils.isBlank(headInfo[1])) {
			// 使用默认宽度
			XSSFSheet.setColumnWidth(i, DEFAUL_COLUMN_WIDTH);
			return;
		}
		// 使用用户设置的列宽进行设置
		XSSFSheet.setColumnWidth(i, Integer.parseInt(headInfo[1]));
	}

	/**
	 * 单元格写值处理器
	 * 写入内存模式
	 * @param {{@link XSSFCell}
	 * @param cellValue
	 *            单元格值
	 *            XSSFWorkbook,SXSSFWorkbook,HSSFWorkbook,
	 *            HSSFSheet,SXSSFSheet,XSSFChartSheet,XSSFDialogsheet,
	 *            XSSFCell,SXSSFCell,HSSFCell
	 *            有三种实现方式:
	 *            XSSFCell:基于内存
	 *            SXSSFCell:基于内存+磁盘的写入方式
	 *            HSSFCell:高水平的写入方式，对于单元格的数据类型有很高的限制，比如:字符串格式只有字符，不能有数字
	 */
	public static void cellValueHandler(Cell cell, Object cellValue) {
		// 判断cellValue是否为空，否则在cellValue.toString()会出现空指针异常
		if (cellValue == null) {
			cell.setCellValue("");
			return;
		}
		if (cellValue instanceof String) {
			cell.setCellValue((String) cellValue);
		} else if (cellValue instanceof Boolean) {
			cell.setCellValue((Boolean) cellValue);
		} else if (cellValue instanceof Calendar) {
			cell.setCellValue((Calendar) cellValue);
		} else if (cellValue instanceof Double) {
			cell.setCellValue((Double) cellValue);
		} else if (cellValue instanceof Integer || cellValue instanceof Long || cellValue instanceof Short
				|| cellValue instanceof Float) {
			cell.setCellValue((Double.parseDouble(cellValue.toString())));
		} else if (cellValue instanceof XSSFRichTextString) {
			cell.setCellValue((XSSFRichTextString) cellValue);
		}
		cell.setCellValue(cellValue.toString());
	}





	/**
	 * 创建标题和表头单元格样式
	 *
	 * @param XSSFWorkbook
	 *            {@link XSSFWorkbook}
	 * @return {@link XSSFCellStyle}
	 */
	private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook XSSFWorkbook) {
		LOGGER.info("【创建标题和表头单元格样式】");
		// 单元格的样式
		XSSFCellStyle cellStyle = XSSFWorkbook.createCellStyle();
		// 设置字体样式，改为不变粗
		XSSFFont font = XSSFWorkbook.createFont();
		font.setFontHeightInPoints((short) 13);
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		// 水平居中
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
		// 垂直居中
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 设置通用的单元格属性
		setCommonCellStyle(cellStyle);
		return cellStyle;
	}

	/**
	 * 创建内容单元格样式
	 *
	 * @param XSSFWorkbook
	 *            {@link XSSFWorkbook}
	 * @return {@link XSSFCellStyle}
	 */
	public static XSSFCellStyle createContentCellStyle(XSSFWorkbook XSSFWorkbook) {
		LOGGER.info("【创建内容单元格样式】");
		// 单元格的样式
		XSSFCellStyle cellStyle = XSSFWorkbook.createCellStyle();
		// 设置字体样式，改为不变粗
		XSSFFont font = XSSFWorkbook.createFont();
		font.setFontHeightInPoints((short) 11);
		cellStyle.setFont(font);
		// 设置单元格自动换行
		cellStyle.setWrapText(true);
		// 水平居中
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
		// 垂直居中
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 设置通用的单元格属性
		setCommonCellStyle(cellStyle);
		return cellStyle;
	}

	/**
	 * 设置通用的单元格属性
	 * 
	 * @param cellStyle
	 *            要设置属性的单元格
	 */
	private static void setCommonCellStyle(XSSFCellStyle cellStyle) {
		// 居中
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		// 设置边框
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
	}

	// UTF-8编码
	private static String toUTF8(String s) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < s.length(); i++) {
			char c = s.charAt(i);
			if (c >= 0 && c <= 255) {
				sb.append(c);
			} else {
				byte[] b;
				try {
					b = Character.toString(c).getBytes("utf-8");
				} catch (Exception ex) {
					System.out.println(ex);
					b = new byte[0];
				}
				for (int j = 0; j < b.length; j++) {
					int k = b[j];
					if (k < 0)
						k += 256;
					sb.append("%" + Integer.toHexString(k).toUpperCase());
				}
			}
		}
		return sb.toString();
	}

	public static boolean deleteFile(String sPath) {
		boolean flag = false;
		File file = new File(sPath);
		// 路径为文件且不为空则进行删除
		if (file.isFile() && file.exists()) {
			file.delete();
			flag = true;
		}
		return flag;
	}

	/**
	 * 根据文件路径读取excel文件
	 * 
	 * @param excelPath
	 *            excel的路径
	 * @param skipRows
	 *            需要跳过的行数
	 * @return List<String[]> 集合中每一个元素是一个数组，按单元格索引存储每个单元格的值，一个元素可以封装成一个需要的java bean
	 * @throws Exception
	 */
	public static List<String[]> readExcel(String excelPath, int skipRows, int columCount) throws Exception {
		LOGGER.info("【读取Excel】excelPath : " + excelPath + " , skipRows : " + skipRows);
		FileInputStream is = new FileInputStream(new File(excelPath));
		POIFSFileSystem fs = new POIFSFileSystem(is);
		// XSSFWorkbook wb = new XSSFWorkbook(fs);
		XSSFWorkbook wb = null;
		List<String[]> list = new ArrayList<String[]>();
		XSSFSheet sheet = wb.getSheetAt(0);
		// 得到总共的行数
		int rowNum = sheet.getPhysicalNumberOfRows();
		try {
			for (int i = skipRows; i < rowNum; i++) {
				String[] vals = new String[columCount];
				XSSFRow row = sheet.getRow(i);
				if (null == row) {
					continue;
				}
				for (int j = 0; j < columCount; j++) {
					XSSFCell cell = row.getCell(j);
					String val = getStringCellValue(cell);
					vals[j] = val;
				}
				list.add(vals);
			}
		} catch (Exception e) {
			LOGGER.error("【Excel解析失败】", e);
			throw new RuntimeException("Excel解析失败");
		} finally {
			wb.close();
		}
		return list;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	public static String getStringCellValue(XSSFCell cell) {
		if (cell == null)
			return "";
		String strCell = "";
		switch (cell.getCellType()) {
		case XSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(format.format(cell.getNumericCellValue())).replace(",", "");
			break;
		case XSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case XSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}

		return strCell.replaceAll("\\s*", "");
	}

	public static Map<String, Object> closeImport(InputStream is, XSSFWorkbook xssfWorkbook, String message)
			throws IOException {
		Map<String, Object> map = new HashMap<String, Object>();
		xssfWorkbook.close();
		is.close();
		map.put("resultKey", message);
		return map;
	}

	// 判断是否为金额
	public static boolean isNumber(String str) {
		java.util.regex.Pattern pattern = java.util.regex.Pattern
				.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$"); // 判断小数点后2位的数字的正则表达式
		java.util.regex.Matcher match = pattern.matcher(str);
		if (match.matches() == false) {
			return false;
		} else {
			return true;
		}
	}

	/**
	 * 读取excel文件 返回 XSSFSheet对象
	 * @param excelPath
	 * @param sheetIndex
	 * @return
	 * @throws IOException
     */
	public static XSSFSheet readExcelSheet(String excelPath,int sheetIndex) throws IOException {
		if(StringUtils.isBlank(excelPath)){
			LOGGER.error("excel file path is null ...");
			return null;
		}
		if(sheetIndex < 0){
			LOGGER.error("sheetIndex must be >=0 ....");
			return null;
		}
		LOGGER.info("【读取Excel】excelPath : " + excelPath + " , sheetIndex : " +sheetIndex);
		is = new FileInputStream(new File(excelPath));
		wb = new XSSFWorkbook(is);

		XSSFSheet sheet = wb.getSheetAt(sheetIndex);

		return sheet;
	}

	//关闭资源
	public static void close() {
		try {
			if (wb != null) {
				wb.close();
			}
			if (is != null) {
				is.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
